package com.example.reagent_management.room.dao

import androidx.paging.PagingSource
import androidx.room.*
import com.example.reagent_management.room.entity.HistoryInfo
import com.example.reagent_management.ui.activity.pojo.RemindDateAndCount
import com.example.reagent_management.ui.statistics.pojo.OperateTimesAndNum
import com.example.reagent_management.ui.statistics.pojo.ReagentNameAndNum

import kotlinx.coroutines.flow.Flow


@Dao
interface HistoryDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertHistory(historyInfo: HistoryInfo):Long

    @Query("select * from history")
    fun getAllHistory():PagingSource<Int,HistoryInfo>

    @Query("select * from history where history_id = :id")
    fun queryById(id:Long):HistoryInfo

    @Query("delete from history where history_id = :id")
    fun deleteById(id: Long): Int

    @Query("update history set counts = :counts where reagents_id = :id and batch= :batch")
    fun updateCounts(batch:String,id: Long,counts:Long)
    /*
    * 获取排名前五的以试剂名为组的出库数总数  使用流数据
    * */
//    @Transaction
    @Query("select name ,sum(num) from history inner join operate on id = operate_id where operate_type = 0 and days between :startMonth and :endMonth group by name  order by sum(num) desc limit 5 ")
    fun getNameAndNum(startMonth:Long,endMonth: Long): Flow<List<ReagentNameAndNum>>

    /**
     * 统计近24小时出库
     */
    @Query("select strftime('%s',times) as time,operator_num  from history where times > datetime('now', 'localtime', '-1 days') and operate_type = 0 order by history_id ")
    fun getHourOutStore():Flow<List<OperateTimesAndNum>>

    /**
     * 统计近24小时入库
     */
    @Query("select strftime('%s',times) as time,operator_num  from history where times > datetime('now', 'localtime', '-1 days') and operate_type = 1 order by history_id ")
    fun getHourInStore():Flow<List<OperateTimesAndNum>>

    /**
     * 统计近7天出库
     */
    @Query("select strftime('%s',times) as time,operator_num from history  where times > datetime('now', 'localtime', '-7 days') and operate_type = :operateType order by history_id ")
    fun getWeekStore(operateType:Boolean):Flow<List<OperateTimesAndNum>>

    @Query("delete from history where times < datetime('now', 'localtime', '-31 days')")
    fun cleanHistory()

    /**
     * 根据信息id修改试剂名
     *
     */
    @Query("update history set name = :reagentName where reagents_id = :id")
    fun updateReagent(id: Long,reagentName:String):Int
}